import glob
import ntpath
import pandas as pd

def vm_handler(row:pd.Series)->pd.Series:
    try:
        row["Вид материала в SAP ERP"] = str(row["Вид материала в SAP ERP"])[0:4]
    except Exception as exp:
        row["Полное наименование материала"]=f"{exp}"
    finally:
        return row

if __name__=="__main__":
    try:
        pattern="D:\\work\\Укрупнение\\current\\data\\2024*\\*.xlsx"
        dump_dir="D:\\work\\Укрупнение\\current\\debug_data\\"
        exclude_pattern="нение_"
        neuro_columns=["ID в SAP ERP VMZ", "Вид материала в SAP ERP","Полное наименование материала"]
        files=[f for f in glob.glob(pattern) if exclude_pattern not in f] if exclude_pattern is not None else [f for f in glob.glob(pattern)]
        data={}
        list_data=[]
        for f in files:
            short_name = ntpath.basename(f)
            print(f"{f} -> {short_name}")
            xl = pd.ExcelFile(f)
            for sheet_name in xl.sheet_names:
                df=xl.parse(sheet_name=sheet_name)
                key = f"{dump_dir}[{short_name.replace('.xlsx','')}][{sheet_name}].xlsx"
                # print(f"\t{key} -> {df.empty}")
                if not df.empty:
                    if key not in data.keys():
                        # data[key]=df
                        df=df[neuro_columns] #.apply(vm_handler,axis=1)
                        list_data.append(df)
                    # else:
                    #     data[key]=pd.concat([data[key],df])
        # for key in data.keys():
        #     df:pd.DataFrame = data[key]
        #     df.to_excel(key,index=False)
        #     print(f"{key} : {df.shape} : done") 
        result = pd.concat(list_data)
        result = result.drop_duplicates()
        result.to_excel(f"{dump_dir}neuro_dump.xlsx",index=False)
        print(f"{result.shape} -> done")   
    except Exception as exp:
        print(f"error: {exp}")